﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;

namespace DataAccess.AccountManage
{
    /// <summary>
    /// 操作类，一般是充值，解锁，获取借车消费等
    /// </summary>
    public class Operate : DbBase, IOperate
    {
        /// <summary>
        /// 通过cpu卡号来获取当前用户借车的消费额，和消费id
        /// </summary>
        /// <param name="cpuno">可以是cpuid或者是cpuno</param>
        /// <param name="bikeid">借车的id</param>
        /// <param name="startTime">开始借车的时间</param>
        /// <returns>"consumeid"表示消费id，"cost"表示消费金额</returns>
        public ResultInfo<Dictionary<string, Decimal>> GetBorrowBikeCost(string cpuno, string bikeid, DateTime startTime)
        {
            var result = new ResultInfo<Dictionary<string, Decimal>>();
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "GetBorrowBikeCost";
                    command.Parameters.Add("@cpuno", SqlDbType.VarChar, 50).Value = cpuno;
                    command.Parameters.Add("@bikeid", SqlDbType.VarChar, 50).Value = bikeid;
                    command.Parameters.Add("@starttime", SqlDbType.DateTime).Value = startTime;
                    command.Parameters.Add("@cost", SqlDbType.Decimal).Direction = ParameterDirection.Output;
                    command.Parameters.Add("@consumeId", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    Decimal temp = Converts.ConvertToDecimal(command.Parameters["@cost"].Value == null ? "" : command.Parameters["@cost"].Value.ToString());
                    if (temp >= 0)
                    {
                        int temp2 = Converts.ConvertToInt(command.Parameters["@consumeId"].Value.ToString());
                        if (temp2 > 0)
                        {
                            result.ResultNumber = ResultEnum.Success;
                            result.Obj = new Dictionary<string, Decimal> { { "consumeid", temp2 }, { "cost", temp } };
                        }
                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "该用户无法解锁";
                    }
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// cpu卡充值（包括了充值表和票据表的操作
        /// </summary>
        /// <param name="initialMoney">初始金额</param>
        /// <param name="amountMoney">充值金额</param>
        /// <param name="cpuno">cpu标识</param>
        /// <param name="operateaddress">充值地点</param>
        /// <param name="operateaddressid">充值点id</param>
        /// <param name="operatepsamid">充值点psamid</param>
        /// <param name="operatorid">充值经手人员</param>
        /// <returns></returns>
        public ResultInfo<bool> RechargeYourPurse(Decimal initialMoney, Decimal amountMoney, string cpuno, string operateaddress, Int64 operateaddressid, string operatepsamid, Int64 operatorid)
        {
            var result = new ResultInfo<bool>();
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "RechargeYourPurse";
                    command.Parameters.Add("@cpuid", SqlDbType.VarChar, 50).Value = cpuno;
                    command.Parameters.Add("@initalMoney", SqlDbType.Decimal).Value = initialMoney;
                    command.Parameters.Add("@amountMoney", SqlDbType.Decimal).Value = amountMoney;

                    command.Parameters.Add("@operateaddress", SqlDbType.VarChar, 50).Value = operateaddress;
                    command.Parameters.Add("@operateaddressid", SqlDbType.BigInt).Value = operateaddressid;
                    command.Parameters.Add("@operatepsamid", SqlDbType.VarChar, 50).Value = operatepsamid;
                    command.Parameters.Add("@operatorid", SqlDbType.BigInt).Value = operatorid;
                    command.Parameters.Add("@returnflag", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    int temp = Convert.ToInt32(command.Parameters["@returnflag"].Value);
                    if (temp == 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = true;
                    }
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 解锁用户卡
        /// </summary>
        /// <param name="costMoney">欠费金额，即此次扣款金额</param>
        /// <param name="cpuno">用户卡id</param>
        /// <param name="consumeId">消费记录的id</param>
        /// <param name="operateaddress"></param>
        /// <param name="operateaddressid"></param>
        /// <param name="operatepsamid"></param>
        /// <param name="operatorid"></param>
        /// <returns></returns>
        public ResultInfo<bool> UnlockCpuCard(Decimal costMoney, string cpuno, int consumeId, string operateaddress, Int64 operateaddressid, string operatepsamid, Int64 operatorid)
        {
            var result = new ResultInfo<bool> { ResultNumber = ResultEnum.Error, Obj = false };
            try
            {
                using (SqlCommand command = GetProCommand())
                {
                    command.CommandText = "UnlockCpuCard";
                    command.Parameters.Add("@cpuid", SqlDbType.VarChar, 50).Value = cpuno;
                    command.Parameters.Add("@costMoney", SqlDbType.Decimal).Value = costMoney;
                    command.Parameters.Add("@consumeId", SqlDbType.Int).Value = consumeId;
                    command.Parameters.Add("@operateaddress", SqlDbType.VarChar, 50).Value = operateaddress;
                    command.Parameters.Add("@operateaddressid", SqlDbType.BigInt).Value = operateaddressid;
                    command.Parameters.Add("@operatepsamid", SqlDbType.VarChar, 50).Value = operatepsamid;
                    command.Parameters.Add("@operatorid", SqlDbType.BigInt).Value = operatorid;
                    command.Parameters.Add("@returnflag", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    int temp = Convert.ToInt32(command.Parameters["@returnflag"].Value);
                    if (temp == 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = true;
                    }
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 充值添加记录
        /// </summary>
        /// <param name="rc">充值明细</param>
        /// <returns></returns>
        public int AddReCharge(recharge rc)
        {
            int result = 0;
            if (rc != null)
            {
                string sql =
                    string.Format(
                        "insert into recharge([receiptid],[cardno],[amount],[chargetime],[initialcash],[balance]) values({0},'{1}',{2},getdate(),{3},{4}) select @@identity id", rc.receiptid, rc.cardno.Replace("\0", ""), rc.amount, rc.initialcash, rc.balance);

                DataSet dataSet = ExcuteSqlDataSet(sql);
                if (dataSet != null)
                {
                    result = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                }
                //CloseConn();
            }
            return result;
        }

        /// <summary>
        /// 添加一个收据
        /// </summary>
        /// <param name="receipt">收据明细</param>
        /// <returns></returns>
        public int AddReceipt(receipt receipt)
        {
            int result = 0;
            if (receipt != null)
            {
                string sql = string.Format("insert into receipt([receiptno],[amount],[receipttype],[memo]) values('{0}',{1},{2},'{3}') select @@identity id", receipt.receiptno.Replace("\0", ""), receipt.amount, receipt.receipttype, receipt.memo.Replace("\0", ""));

                DataSet dataSet = ExcuteSqlDataSet(sql);
                if (dataSet != null)
                {
                    result = Convert.ToInt32(dataSet.Tables[0].Rows[0]["id"]);
                }
                //CloseConn();
            }
            return result;
        }

        /// <summary>
        /// 查询数据库中的消费记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="cpuid">cpu卡标识</param>
        /// <param name="idCardNo">身份证号</param>
        /// <param name="userName">用户名</param>
        /// <param name="telephone">电话号码</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<jsh_consume>> GetConsumes(int pageIndex, int pageSize, string cpuid, string idCardNo, string userName, string telephone)
        {
            var result = new ResultInfo<PagingDataSet<jsh_consume>>() { ResultNumber = ResultEnum.Error };
            if (!string.IsNullOrEmpty(cpuid) || !string.IsNullOrEmpty(idCardNo) || !string.IsNullOrEmpty(userName) || !string.IsNullOrEmpty(telephone))
            {
                try
                {
                    using (SqlCommand myCommand = GetProCommand())
                    {
                        myCommand.CommandText = "Common_GetPagingRecords";
                        StringBuilder fromClause = new StringBuilder();
                        fromClause.Append("jsh_consume with (nolock) ");
                        StringBuilder whereClause = new StringBuilder();
                        if (!string.IsNullOrEmpty(cpuid))
                        {
                            whereClause.Append(string.Format(" and cpuid='{0}' ", cpuid));
                        }
                        if (!string.IsNullOrEmpty(idCardNo))
                        {
                            whereClause.Append(
                                string.Format(" and cpuid in (select cpuid from jsh_account where zzhm='{0}') ",
                                    idCardNo));
                        }
                        if (!string.IsNullOrEmpty(userName))
                        {
                            whereClause.Append(
                                string.Format(" and cpuid in (select cpuid from jsh_account where username='{0}') ",
                                    userName));
                        }
                        if (!string.IsNullOrEmpty(telephone))
                        {
                            whereClause.Append(
                                string.Format(
                                    " and cpuid in (select cpuid from jsh_account where [telephone]='{0}' or [mobile]='{0}') ",
                                    telephone));
                        }
                        if (whereClause.Length > 3)
                        {
                            whereClause.Remove(0, 5);
                        }

                        myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                        myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                        myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "*";
                        myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value =
                            whereClause.ToString();
                        myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "id";
                        myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                        myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                        myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                        var dataSet = new DataSet();
                        var da = new SqlDataAdapter(myCommand);
                        da.Fill(dataSet);

                        var list = dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new jsh_consume(dataRow)).Where(temp => temp.TranByConstructFlag).ToList();

                        var totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                        var reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                        if (reverseOrder)
                            list.Reverse();

                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = new PagingDataSet<jsh_consume>
                        {
                            PageIndex = pageIndex,
                            PageSize = pageSize,
                            Records = list,
                            TotalRecords = totalRecords
                        };
                    }
                }
                catch (SqlException)
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "数据库错误，请联系管理员！";
                }
                catch (Exception e)
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = e.Message;
                }
            }
            else
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "请输入至少一个查询条件";
            }
            return result;
        }

        /// <summary>
        /// 获取当前充值的最新一条记录
        /// </summary>
        /// <param name="cpuno">cpu卡号</param>
        /// <returns></returns>
        public ResultInfo<recharge> GetCurrentRecharge(string cpuno)
        {
            ResultInfo<recharge> result=new ResultInfo<recharge>(){ResultNumber = ResultEnum.Error};

            if (!string.IsNullOrEmpty(cpuno))
                cpuno = cpuno.Replace("\0","");
            string sql = string.Format("select top 1 * from recharge where cardno='{0}' order by chargetime desc", cpuno);
            DataSet dataSet = ExcuteSqlDataSet(sql);
            if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
            {
                var re = new recharge(dataSet.Tables[0].Rows[0]);
                if (re.TranByConstructFlag)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = re;
                }else
                {
                    result.Describe = "没有获取到最新的充值记录";
                }
            }
            //CloseConn();
            return result;
        }
    }
}
